import sqlite3


def db_init(db_name=":memory:"):
    conn = sqlite3.connect(db_name)
    cursor = conn.cursor()

    # Создание таблиц
    cursor.execute(
        """CREATE TABLE IF NOT EXISTS dep 
            (id INTEGER PRIMARY KEY, 
            name TEXT)"""
    )
    cursor.execute(
        """CREATE TABLE IF NOT EXISTS dep2 
            (id INTEGER, 
            name TEXT)"""
    )
    cursor.execute(
        """CREATE TABLE IF NOT EXISTS emp 
            (id INTEGER PRIMARY KEY, 
            name TEXT, 
            dep_id INTEGER, 
            age INTEGER, 
            salary DECIMAL, 
            FOREIGN KEY(dep_id) REFERENCES dep(id))"""
    )
    cursor.execute(
        """CREATE TABLE IF NOT EXISTS fin (
            month INTEGER, 
            emp_id INTEGER, 
            salary DECIMAL, 
            sum_salary DECIMAL, 
            FOREIGN KEY(emp_id) REFERENCES emp(id))"""
    )

    # Заполнение таблиц данными
    dep_data = [
        (1, "Бухгалтерия"),
        (2, "Кадры")
    ]
    dep2_data = [
        (1, "Бухгалтерия 1"),
        (1, "Бухгалтерия 2"),
        (1, "Бухгалтерия 3"),
        (1, "Бухгалтерия 4"),
        (1, "Бухгалтерия 5"),
        (2, "Кадры 1"),
        (2, "Кадры 2"),
        (2, "Кадры 3")
    ]
    emp_data = [
        (1, "Иванов", 1, 45, 100),
        (2, "Петров", 1, 34, 86),
        (3, "Петров", 1, 18, 75),
        (4, "Петров", 1, 56, 92),
        (5, "Сидоров", 1, 28, 101),
        (6, "Сидоров", 1, 37, 79),
        (7, "Сидоров", 2, 31, 55),
        (8, "Сидоров", 2, 30, 67),
        (9, "Федотов", 2, 18, 43),
    ]
    fin_data = [
        (1, 1, 50),
        (2, 1, 50),
        (3, 1, 50),
        (1, 2, 60),
        (2, 2, 60),
        (3, 2, 60),
    ]

    cursor.executemany(
        """INSERT INTO dep (id, name) VALUES (?, ?)""",
        dep_data,
    )
    cursor.executemany(
        """INSERT INTO dep2 (id, name) VALUES (?, ?)""",
        dep2_data,
    )
    cursor.executemany(
        """INSERT INTO emp (id, name, dep_id, age, salary) VALUES (?, ?, ?, ?, ?)""",
        emp_data,
    )
    cursor.executemany(
        """INSERT INTO fin (month, emp_id, salary) VALUES (?, ?, ?)""",
        fin_data,
    )

    conn.commit()
    cursor.close()
    conn.close()


if __name__ == "__main__":
    db_init("local_db.sqlite")
